package com.yss.sofa.report.engine;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jackson.map.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.yss.sofa.report.engine.cell.page.PageCell;
import com.yss.sofa.report.engine.cell.visible.VisibleCell;
import com.yss.sofa.report.engine.cell.visible.result.ResultCell;
import com.yss.sofa.report.engine.core.Block;
import com.yss.sofa.report.engine.core.Matrix;
import com.yss.sofa.report.engine.core.Piston;
import com.yss.sofa.report.engine.poi.SheetUtils;
import com.yss.sofa.report.engine.util.ArrayUtil;
import com.yss.sofa.report.engine.util.DateUtil;
import com.yss.sofa.report.engine.util.DbfUtil;
import com.yss.sofa.report.engine.util.Excel2003Util;
import com.yss.sofa.report.engine.util.ImageUtil;
import com.yss.sofa.report.engine.util.PdfUtil;
import com.yss.sofa.report.engine.util.PrintUtil;
import com.yss.sofa.report.engine.util.WordUtil;
import com.yss.sofa.report.engine.util.XmlUtil;

public final class Engine {
	private static final Logger LOGGER=LoggerFactory.getLogger(Engine.class);
	
	/**
	 * 读取模板
	 * @param templateBytes 模板文件二进制
	 * @return 模板对象
	 * @throws Exception 
	 */
	public static Template loadTemplateByDataBase(byte[] templateBytes)
			throws Exception {
		Template template = null;

		InputStream in = new ByteArrayInputStream(templateBytes);
		boolean isXssWorkbook = EngineUtil.hasOOXMLHeader(in);
		if (isXssWorkbook) {
			try {
				XSSFWorkbook excel07 = new XSSFWorkbook(in);
				template = Piston.loadTemplate(excel07);
				return template;
			} catch (Exception e) {
				LOGGER.info("尝试构建excel07模板失败");
				throw new Exception("构建模板失败", e);
			}
		} else {
			try {
				HSSFWorkbook excel03 = new HSSFWorkbook(in);
				template = Piston.loadTemplate(excel03);
				return template;
			} catch (Exception e) {
				LOGGER.info("尝试构建excel03模板失败");
				throw new Exception("构建模板失败", e);
			}
		}
	}

	/**
	 * 读取模板
	 * @param templateBytes 模板文件二进制
	 * @param customBytes 客户模板文件二进制
	 * @return 模板对象
	 * @throws Exception
	 */
	public static Template loadTemplateByDataBase(byte[] templateBytes,byte[] customBytes,String customTemplateType) throws Exception{
		if("2".equals(customTemplateType)){
			try {
				Workbook sofa=new XSSFWorkbook(new ByteArrayInputStream(templateBytes));
				Workbook custom=new XSSFWorkbook(new ByteArrayInputStream(customBytes));
//				
//				//这个流从response获取，再配置响应头信息就可以实现下载；
//				OutputStream out = null;
//				try {
//					out = new FileOutputStream(new File("E:/download", "test8888.xlsx"));
//				} catch (FileNotFoundException e1) {
//					e1.printStackTrace();
//				}
//				try {
//					custom.write(out);
//
//				} catch (IOException e) {
//					e.printStackTrace();
//				}
				
				return Piston.loadCustomTemplate(sofa,custom);
			} catch (Exception e) {
				LOGGER.info("尝试用excel07构建客户模板");
			}
		}else{
			try {
				Workbook sofa=new HSSFWorkbook(new ByteArrayInputStream(templateBytes));
				Workbook custom=new HSSFWorkbook(new ByteArrayInputStream(customBytes));
				return Piston.loadCustomTemplate(sofa,custom);
			} catch (Exception e) {
				throw new Exception("尝试用excel03构建客户模板失败", e);
			}
		}
	    return null;
	}
	
	
	/**
	 * 读取模板
	 * @param filename 模板文件全路径
	 * @param customBytes 客户模板vo
	 * @return 模板对象
	 * @throws Exception
	 */
	public static Template loadTemplate(String filename,byte[] customBytes,String customTemplateType) throws Exception{
		if("2".equals(customTemplateType)){
			try {
				Workbook sofa=new XSSFWorkbook(new FileInputStream(filename));
				Workbook custom=new XSSFWorkbook(new ByteArrayInputStream(customBytes));
				return Piston.loadCustomTemplate(sofa,custom);
			} catch (Exception e) {
				LOGGER.info("尝试使用excel07加载客户模板失败["+filename+"]");
			}
		}else{
			try {
				Workbook sofa=new HSSFWorkbook(new FileInputStream(filename));
				Workbook custom=new HSSFWorkbook(new ByteArrayInputStream(customBytes));
				return Piston.loadCustomTemplate(sofa,custom);
			} catch (Exception e) {
				throw new Exception("尝试使用excel03加载客户模板失败["+filename+"]");
			}
		}
		
		return null;
	}

	/**
	 * 读取模板
	 * @param filename 模板文件全路径
	 * @return 模板对象
	 * @throws Exception 
	 */
	public static Template loadTemplate(String filename) throws Exception {
		LOGGER.info("load template from file [" + filename + "]");
		Template template = null;
		InputStream in = new FileInputStream(filename);
		boolean isXssWorkbook = EngineUtil.hasOOXMLHeader(in);
		if (isXssWorkbook) {
			try {
				Workbook excel = new XSSFWorkbook(new FileInputStream(filename));
				template = Piston.loadTemplate(excel);
			} catch (Exception e) {
				LOGGER.info("尝试用excel07模板加载 [" + filename + "]");
				throw new Exception("文件[" + filename + "]不是有效的(excel2007+)文件",e);
			}
		} else {
			try {
				Workbook excel = new HSSFWorkbook(new FileInputStream(filename));
				template = Piston.loadTemplate(excel);
			} catch (Exception e) {
				throw new Exception("文件[" + filename + "]不是有效的(excel2003+)文件",e);
			}
		}
		return template;
	}

	
	/**
	 * 反编译报表，写入模板到指定文件
	 * @param report 报表对象
	 * @param filename 文件全路径
	 * @throws Exception
	 */
	public static void decompile(Report report,String filename) throws Exception{
		LOGGER.info("decompile report "+report.toLog()+" to file ["+filename+"]");
		FileOutputStream file=new FileOutputStream(filename);
		try {
			Workbook excel=Piston.decompile(report);
			excel.write(file);
			excel.close();
		} finally {
			file.close();
		}
	}
	/**
	 * 重新执行报表
	 * @param report 报表对象
	 * @param context 报表上下文
	 * @return 报表对象
	 * @throws Exception
	 */
	public static Report reExecuteMain(Report report,Context context) throws Exception{
		LOGGER.info("re-execute report "+report.toLog());
		//consts
		Workbook excel=Piston.decompile(report);
		Template template=Piston.loadTemplate(excel);
		Map<String,String> params=new HashMap<String, String>();
		params.putAll(report.getRawParams());
		Map<String,String> consts=new HashMap<String, String>();
		consts.putAll(report.getConsts());
		context.put(Context.CONTEXT_KEY_CONSTS, consts);
		return executeMain(template, context, params);
	}
	/**
	 * 反编译报表，写入模板流
	 * @param report 报表对象
	 * @param out 输出流
	 * @throws Exception
	 */
	public static void decompile(Report report,OutputStream out) throws Exception{
		LOGGER.info("decompile report "+report.toLog()+" to "+out.getClass().getName());
		Workbook excel=Piston.decompile(report);
		try {
			excel.write(out);
			out.flush();
		} finally {
			excel.close();
		}
	}
	/**
	 * 导出/写入到指定文件
	 * @param report 报表对象
	 * @param filename 文件全路径
	 * @param type 文件类型（excel、word、pdf等）
	 * @throws Exception 
	 */
	public static void export(Report report,String filename,int type) throws Exception{
		LOGGER.info("export report "+report.toLog()+" to file ["+filename+"] as type ["+type+"]");
		FileOutputStream file=new FileOutputStream(filename);
		try {
			switch (type) {
			case Const.FILE_TYPE_STUB:{
				ObjectOutputStream output=new ObjectOutputStream(file);
				try {
					output.writeObject(report);
					file.flush();
				} finally {
					output.close();
				}
				break;
			}
			case Const.FILE_TYPE_TEXT:{
				Map<String,String[][]> raws=raws(report);
				StringBuilder txt=new StringBuilder();
				for(String output:raws.keySet()){
					txt.append('`').append(output).append('`').append("\r\n");
					String[][] raw=raws.get(output);
					for(String[] row:raw){
						for(String value:row){
							txt.append(value==null?"null":value.replaceAll("\t", " ").replaceAll("\n", " ").replaceAll("\r", "")).append("\t");
						}
						txt.delete(txt.length()-1, txt.length());
						txt.append("\r\n");
					}
					
				}
				txt.delete(txt.length()-2, txt.length());
				file.write(txt.toString().getBytes("UTF-8"));
				file.flush();
				break;
			}
			
			case Const.FILE_TYPE_CUSTOM_XML:{
				XmlUtil.export(report, filename);
				break;
			}
			
			case Const.FILE_TYPE_DBF: {
				DbfUtil.export(report, filename);
				break;
			}

			case Const.FILE_TYPE_EXCEL:{
				Workbook excel=Piston.exportExcel(report);
				excel.write(file);
				file.flush();
				excel.close();
				break;
			}
			case Const.FILE_TYPE_CUSTOM_EXCEL:{
				Workbook excel=Piston.exportExcel(report);
				excel.write(file);
				file.flush();
				excel.close();
				break;
			}
			case Const.FILE_TYPE_PDF:{
				Workbook excel = report.getExcel(true);
				boolean isHSSFWorkbook = excel instanceof HSSFWorkbook;
				if (!isHSSFWorkbook) {
					PdfUtil.export(report, filename);
				}
				break;
			}
			case Const.FILE_TYPE_WORD: {
				Workbook excel = report.getExcel(true);
				boolean isHSSFWorkbook = excel instanceof HSSFWorkbook;
				if (!isHSSFWorkbook) {
					WordUtil.export(report, filename);
				}
				break;
			}
			case Const.FILE_TYPE_PNG:{
				Workbook excel = report.getExcel(true);
				boolean isHSSFWorkbook = excel instanceof HSSFWorkbook;
				if (!isHSSFWorkbook) {
				  ImageUtil.export(report, filename);
				}
				break;
			}
			case Const.FILE_TYPE_EXCEL_2003:{
				Excel2003Util.export(report, filename);
				break;
			}
			default:
				break;
			}
		} finally {
			file.close();
		}
	}
	/**
	 * 写入到流
	 * @param report 报表对象
	 * @param out 输出流
	 * @param type 文件类型（excel、word、pdf等）
	 * @throws Exception
	 */
	public static void export(Report report,OutputStream out,int type) throws Exception{
		LOGGER.info("export report "+report.toLog()+" to "+out.getClass().getName()+" as type ["+type+"]");
		switch (type) {
		case Const.FILE_TYPE_STUB:{
			ObjectOutputStream output=new ObjectOutputStream(out);
			try {
				output.writeObject(report);
				out.flush();
			} finally {
				output.close();
			}
			break;
		}
		case Const.FILE_TYPE_TEXT:{
			Map<String,String[][]> raws=raws(report);
			StringBuilder txt=new StringBuilder();
			for(String output:raws.keySet()){
				txt.append('`').append(output).append('`').append("\r\n");
				String[][] raw=raws.get(output);
				for(String[] row:raw){
					for(String value:row){
						txt.append(value==null?"null":value.replaceAll("\t", " ").replaceAll("\n", " ").replaceAll("\r", "")).append("\t");
					}
					txt.delete(txt.length()-1, txt.length());
					txt.append("\r\n");
				}
			}
			txt.delete(txt.length()-2, txt.length());
			out.write(txt.toString().getBytes("UTF-8"));
			out.flush();
			break;
		}
		
		case Const.FILE_TYPE_DBF:{
			DbfUtil.export(report, out);
			out.flush();
			break;
		}
		
		case Const.FILE_TYPE_CUSTOM_XML:{
			XmlUtil.export(report, out);
			out.flush();
			break;
		}
		case Const.FILE_TYPE_EXCEL:{
			Workbook excel=Piston.exportExcel(report);
			excel.write(out);
			out.flush();
			excel.close();
			break;
		}
		case Const.FILE_TYPE_CUSTOM_EXCEL:{
			Workbook excel=Piston.exportExcel(report);
			excel.write(out);
			out.flush();
			excel.close();
			break;
		}
		case Const.FILE_TYPE_PDF:{
//			Workbook excel=Piston.exportExcel(report);
//			//excel.write(out);
//			
//			ByteArrayOutputStream out1=new ByteArrayOutputStream();
//			try {
//				excel.write(out1);
//				out1.flush();
//				//return out.toByteArray();
//			} finally {
//				out1.close();
//			}
//			
//			ByteArrayInputStream in = new ByteArrayInputStream(out1.toByteArray());
//			Workbook excel2=new XSSFWorkbook(in);
			
			Workbook excel = report.getExcel(true);
			boolean isHSSFWorkbook = excel instanceof HSSFWorkbook;
			if (!isHSSFWorkbook) {
				PdfUtil.export(report, out);
				out.flush();
			}
			break;
		}
		case Const.FILE_TYPE_WORD:{
			Workbook excel = report.getExcel(true);
			boolean isHSSFWorkbook = excel instanceof HSSFWorkbook;
			if (!isHSSFWorkbook) {
				WordUtil.export(report, out);
				out.flush();
			}
			break;
		}
		case Const.FILE_TYPE_PNG:{
			Workbook excel = report.getExcel(true);
			boolean isHSSFWorkbook = excel instanceof HSSFWorkbook;
			if (!isHSSFWorkbook) {
				ImageUtil.export(report, out);
				out.flush();
			}
			break;
		}
		case Const.FILE_TYPE_EXCEL_2003:{
			Excel2003Util.export(report, out);
			out.flush();
			break;
		}
		default:
			break;
		}
	}
	/**
	 * 读取报表（存根）
	 * @param filename 存根文件全路径
	 * @return 报表对象
	 */
	public static Report loadReport(String filename) throws Exception{
		LOGGER.info("load report from file ["+filename+"]");
		FileInputStream file=new FileInputStream(filename);
		ObjectInputStream input=new ObjectInputStream(file);
		try {
			Report report=(Report)input.readObject();
			report.getExcel(false);
			return report;
		} finally {
			input.close();
			file.close();
		}
	}
	/**
	 * 保存报表（存根）
	 * @param filename 存根文件全路径
	 * @param report 报表对象
	 */
	public static void saveReport(Report report,String filename) throws Exception{
		LOGGER.info("save report "+report.toLog()+" to file ["+filename+"]");
		FileOutputStream file=new FileOutputStream(filename);
		ObjectOutputStream output=new ObjectOutputStream(file);
		try {
			report.closeExcel();
			output.writeObject(report);
		} finally {
			output.close();
			file.close();
		}
	}
	/**
	 * 页面预览
	 * @param report 报表对象
	 * @return HTML
	 * @throws Exception
	 */
	public static String preview(String sheet,Report report,float zoom,int offset,int limit) throws Exception{
		LOGGER.info("preview report "+report.toLog()+".["+sheet+"] as zoom ["+zoom+"]");
		if(Const.MAIN.equals(report.getOutput())){
			if(zoom<=0.1f){
				zoom=0.1f;
			}
			if(report.isEmptyReportSheet(sheet)){
				return "<p style=\"font-size:20px;font-family:微软雅黑;color:#ffc000;\">无内容</p>";
			} else {
				return Piston.previewMainSheet(sheet,report,zoom,offset,limit);
			}
		} else if(Const.FORM.equals(report.getOutput())){
			if(report.isEmptyReportSheet(sheet)){
				return "<p style=\"font-size:16px;font-family:微软雅黑;color:#ffc000;\">无查询条件，可直接提交</p>";
			} else {
				return Piston.previewFormSheet(sheet,report);
			}
		} else {
			throw new Exception("无法输出任何内容，无效的输出指令：report.output=["+report.getOutput()+"]");
		}
	}
	/**
	 * 打印
	 * @param output 报表sheet名称
	 * @param report 报表对象
	 * @param dpi 屏幕分辨率
	 * @return 打印文档对象
	 * @throws Exception
	 */
	public static Serializable print(String output,Report report,int dpi) throws Exception{
		LOGGER.info("print report["+output+"] "+report.toLog()+" as dpi ["+dpi+"]");
		if(!Const.MAIN.equalsIgnoreCase(report.getOutput())){
			throw new Exception("无法打印报表["+report.getReportName()+"].["+output+"]");
		}
		return PrintUtil.toPrintDocument(output,report,dpi);
	}
	/**
	 * 打印
	 * @param report 报表对象
	 * @param dpi 屏幕分辨率
	 * @return 打印文档对象，key=报表sheet名称
	 * @throws Exception
	 */
	public static Map<String,? extends Serializable> prints(Report report,int dpi) throws Exception{
		LOGGER.info("print report "+report.toLog()+" as dpi ["+dpi+"]");
		if(!Const.MAIN.equalsIgnoreCase(report.getOutput())){
			throw new Exception("无法打印报表["+report.getReportName()+"]，此报表的内容不是主体内容（main）");
		}
		return PrintUtil.toPrintDocuments(report,dpi);
	}
	/**
	 * 返回指定sheet的报表数据，[1][2]=第2行第3列，index从0开始。
	 * @param output 报表sheet名称
	 * @param report 报表对象
	 * @return 数据数组，二进制数据（图片等）=""
	 * @throws Exception
	 */
	public static String[][] raw(String output,Report report) throws Exception{
		//LOGGER.info("report["+sheet+"] "+report.toLog()+" to raw");
		return Piston.toRaw(output, report);
	}
	/**
	 * 返回指定报表数据，[1][2]=第2行第3列，index从0开始。
	 * @param report 报表对象
	 * @return key=sheet名称，value=数据数组，二进制数据（图片等）=""
	 * @throws Exception
	 */
	public static LinkedHashMap<String,String[][]> raws(Report report) throws Exception{
		//LOGGER.info("report "+report.toLog()+" to raw");
		return Piston.toRaws(report);
	}
	/**
	 * 执行报表
	 * @param template 模板对象
	 * @param context 报表上下文
	 * @param params 报表参数
	 * @return 报表对象
	 * @throws Exception
	 */
	public static Report executeMain(Template template,Context context,Map<String,String> params) throws Exception{
		//consts
		@SuppressWarnings("unchecked")
		Map<String,String> consts=context.get(Context.CONTEXT_KEY_CONSTS, Map.class);
		//logger
		try {
			Map<String,Object> log=new LinkedHashMap<String, Object>();
			log.put("params", params);
			log.put("consts", consts);
			ObjectMapper mapper=new ObjectMapper();
			mapper.configure(org.codehaus.jackson.map.SerializationConfig.Feature.INDENT_OUTPUT, false);
			LOGGER.info("execute main template by "+mapper.writeValueAsString(log));
		} catch(Throwable _) {
		}
		//params
		Map<String, Serializable> ps;
		{
			try {
				ps = Piston.convertAndFilterParams(template.getMainParams(), params, false);
			} catch (Exception e) {
				throw new Exception("sheet["+Const.MAIN_META+"]的参数数据类型转换失败", e);
			}
		}
		//before
		if(!Piston.executeMetaBefore(Const.MAIN_META,context,template.getMainMeta().getBefore(),consts,params,ps)){
			throw new Exception("sheet["+Const.MAIN_META+"].meta.before运行结果为FLASE，停止执行");
		}
		//datas
		Map<String,DataSetData> datas=Piston.executeMetaDatas(Const.MAIN_META, context, template.getMainDatasets(), consts, ps, true);
		//after
		if(!Piston.executeMetaAfter(Const.MAIN_META, context, template.getMainMeta().getAfter(), consts, params, ps, datas)){
			throw new Exception("sheet["+Const.MAIN_META+"].meta.after运行结果为FLASE，停止执行");
		}
		//init mains
		Report report=new Report(template.getMiddle(),datas,ps,params,consts,Const.MAIN);
		Set<String> froms=template.getMainSheetNames();
		for(String from:froms){
			PageCell page=Piston.initPage(from, context, template.getMainPage(from), consts, ps, datas, Const.MAIN.equals(from));
			Piston.initFloating(page, consts, ps, datas);
			Set<VisibleCell> cells=Piston.initCells(context, template.getMainCells(from), page, consts, ps, datas);
			report.add(page, cells);
		}
		//for formula cell offset
		Map<String,Integer> formulaDataSetNumMap = new HashMap<String,Integer>();
		for (Entry<String, Integer> datasetRow : template.getDataSetsRows()) {
			String datasetName = datasetRow.getKey();
			if (!StringUtils.isEmpty(datasetName) && null != report.getDatas().get(datasetName)) {
				Integer dataRow = report.getDatas().get(datasetRow.getKey()).getCount();
				formulaDataSetNumMap.put(datasetName, dataRow);
			}
		}
		//execute mains
		for(String from:froms){
			String to=report.getSheetMapping().get(from);
			PageCell page=report.getPage(to);
			Set<VisibleCell> cells=report.getCells(to);
			for(VisibleCell cell:cells){
				Block block=Piston.executeMainCell(page, cell, datas);
				if(block!=null){
					report.append(to,block);
				}
			}
			report.pack(to,template.getDataSetsRows(),formulaDataSetNumMap);
			if(page.isDynamicMerge()){
				Piston.initForceMerge(page,report, ps,consts, datas);
				report.doForceMerge(to);
			}
			page.setRowCount(report.getRowCount(to));
		}
		report.setCustomTemplateType(template.getCustomTemplateType());
		Workbook excel=Piston.generateMiddle(report);
		for(String from:froms){
			String to=report.getSheetMapping().get(from);
			PageCell page=report.getPage(to);
			Piston.generateImage(page,report,excel);
			Piston.generateFloating(page,report,excel);
			if (from.equals(Const.MAIN) && template.getMainPicDataSetsOffset() != null) {
				Piston.writePicWithDatasetOffset(excel.getSheet(from), excel.getSheet(to),template.getMainPicDataSetsOffset(), report.getDatas());
			}
		}
		report.updateMiddle(excel);
		return report;
	}
	/**
	 * 执行报表
	 * @param template 模板对象
	 * @param context 报表上下文
	 * @param params 报表参数
	 * @return 报表对象
	 * @throws Exception
	 */
	public static Report executeForm(Template template,Context context,Map<String,String> params) throws Exception{
		//consts
		@SuppressWarnings("unchecked")
		Map<String,String> consts=context.get(Context.CONTEXT_KEY_CONSTS, Map.class);
		//logger
		try {
			Map<String,Object> log=new LinkedHashMap<String, Object>();
			log.put("params", params);
			log.put("consts", consts);
			ObjectMapper mapper=new ObjectMapper();
			mapper.configure(org.codehaus.jackson.map.SerializationConfig.Feature.INDENT_OUTPUT, false);
			LOGGER.info("execute form template by "+mapper.writeValueAsString(log));
		} catch(Throwable _) {
		}
		//params
		Map<String, Serializable> ps;
		{
			try {
				ps = Piston.convertAndFilterParams(template.getFormParams(), params, true);
			} catch (Exception e) {
				throw new Exception("sheet["+Const.FORM_META+"]的参数数据类型转换失败", e);
			}
		}
		//before
		if(!Piston.executeMetaBefore(Const.FORM_META,context,template.getFormMeta().getBefore(),consts,params,ps)){
			throw new Exception("sheet["+Const.FORM_META+"].meta.before运行结果为FLASE，停止执行");
		}
		//datas，form不再支持虚拟数据集
		Map<String,DataSetData> datas=Piston.executeMetaDatas(Const.FORM_META, context, template.getFormDatasets(), consts, ps, false);
		//after
		if(!Piston.executeMetaAfter(Const.FORM_META, context, template.getFormMeta().getAfter(), consts, params, ps, datas)){
			throw new Exception("sheet["+Const.FORM_META+"].meta.after运行结果为FLASE，停止执行");
		}
		//init form
		Report report=new Report(template.getMiddle(),datas,ps,params,consts,Const.FORM);
		PageCell page=Piston.initPage(Const.FORM, context, template.getFormPage(), consts, ps, datas, false);
		{
			page.setPageCount(1);
			page.setPageTotal(1);
			page.setTitleCount(0);
			page.setHeaderCount(0);
			page.setFooterCount(0);
			page.setIncludePHF(false);
			page.setDynamicAttr(false);
			page.setDynamicHTML(false);
			page.setDynamicMerge(false);
			page.setDynamicSize(false);
			page.setDynamicStyle(false);
			page.setDynamicVisible(false);
			page.setDynamicZoom(false);
			page.getAttr().clear();
			page.getFloatings().clear();
			page.getFloatingCells().clear();
		}
		Set<VisibleCell> cells=Piston.initCells(context, template.getFormCells(), page, consts, ps, datas);
		report.add(page, cells);
		//execute form
		for(VisibleCell cell:cells){
			Block block=Piston.executeFormCell(page, cell, datas);
			if(block!=null){
				report.append(page.getName(),block);
			}
		}
		/*
		report.pack(Const.FORM);
		if(report.getPage(Const.FORM).isDynamicMerge()){
			Piston.initForceMerge(Const.FORM,report, ps,consts, datas);
			report.doForceMerge(Const.FORM);
		}
		*/
		page.setRowCount(report.getRowCount(report.getSheetMapping().get(Const.FORM)));
		report.setCustomTemplateType(template.getCustomTemplateType());
		Piston.generateMiddle(report);
		/*
		Piston.generateImage(Const.FORM,report,excel);
		Piston.generateFloating(Const.FORM,report,excel);
		*/
		return report;
	}
	/**
	 * 执行报表DataSet
	 * @param template 模板对象
	 * @param context 报表上下文
	 * @param params 报表参数
	 * @param dataset 数据集code
	 * @return 数据集数据
	 * @throws Throwable
	 */
	public static DataSetData executeFormDataSet(Template template,Context context,Map<String,String> params,String datasetCode) throws Exception{
		//consts
		@SuppressWarnings("unchecked")
		Map<String,String> consts=context.get(Context.CONTEXT_KEY_CONSTS, Map.class);
		//logger
		try {
			Map<String,Object> log=new LinkedHashMap<String, Object>();
			log.put("params", params);
			log.put("consts", consts);
			ObjectMapper mapper=new ObjectMapper();
			mapper.configure(org.codehaus.jackson.map.SerializationConfig.Feature.INDENT_OUTPUT, false);
			LOGGER.info("execute form dataset ["+datasetCode+"] by "+mapper.writeValueAsString(log));
		} catch(Throwable _) {
		}
		//params
		Map<String, Serializable> ps;
		{
			try {
				ps = Piston.convertAndFilterParams(template.getFormParams(), params, true);
				//带上额外的参数
				ps.putAll(params);
			} catch (Exception e) {
				throw new Exception("sheet["+Const.FORM_META+"]的参数数据类型转换失败", e);
			}
		}
		//before
		if(!Piston.executeMetaBefore(Const.FORM_META,context,template.getFormMeta().getBefore(),consts,params,ps)){
			throw new Exception("sheet["+Const.FORM_META+"].meta.before运行结果为FLASE，停止执行");
		}
		//data，form不再支持虚拟数据集
		Map<String,DataSetData> datas=Piston.executeSingleMetaData(Const.FORM_META, datasetCode, context, template.getFormDatasets(), consts, ps);
		//after
		if(!Piston.executeMetaAfter(Const.FORM_META, context, template.getFormMeta().getAfter(), consts, params, ps, datas)){
			throw new Exception("sheet["+Const.FORM_META+"].meta.after运行结果为FLASE，停止执行");
		}
		return datas.get(datasetCode);
	}
	
	/**
	 * 加载excel文件的数据作为生成新的存根
	 * @param template
	 * @param context
	 * @param params
	 * @param excelFile
	 * @return
	 * @throws Exception
	 */
	public static Report loadStubFile(Template template, Context context, Map<String, String> params, File excelFile)
		throws Exception {
	
		InputStream in = new FileInputStream(excelFile);
		boolean isXssWorkbook = EngineUtil.hasOOXMLHeader(in);
		
		Workbook excelStub = null;
		
		if (isXssWorkbook) {
			try {
				excelStub = new XSSFWorkbook(new FileInputStream(excelFile));
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				in.close();
			}
		} else {
			try {
				excelStub = new HSSFWorkbook(new FileInputStream(excelFile));
			}catch (Exception e) {
				e.printStackTrace();
			} finally {
				in.close();
			}
		}

		
		Sheet sourceSheet = excelStub.getSheetAt(0);
		FormulaEvaluator evaluator = excelStub.getCreationHelper().createFormulaEvaluator();


		Report report = executeMain(template, context, params);
		Workbook excel = report.getExcel(false);
		String to = report.getSheetMapping().get(Const.MAIN);
		Sheet sheet = excel.getSheet(to);

		//int rowCount = report.getRowCount(to);
		//int colCount = report.getColCount(to);
		// XSSFWorkbook excel1 = report.getExcel(false);
		// XSSFFormulaEvaluator evaluator =
		// excel.getCreationHelper().createFormulaEvaluator();
		// XSSFSheet sheet1 = excel.getSheet(to);
		// String[][] result = new String[rowCount][colCount];

		
		/////////////--------////////////
		
		int basicRowNumber = sheet.getLastRowNum() + 1;
		int lastRowNumber = sourceSheet.getLastRowNum() + 1;
		Matrix matrix = report.getMatrixs().get(report.getSheetMapping().get(Const.MAIN));
		int colCount = matrix.getData()[0].length;

		if (lastRowNumber > basicRowNumber) {
			ResultCell[][] newDataArray = (ResultCell[][]) ArrayUtil.resize2DArray(matrix.getData(), lastRowNumber, colCount);
			for (int i = 0; i < newDataArray.length; i++) {
				if (null == newDataArray[i]) {
					newDataArray[i] = new ResultCell[colCount];
				}
				for (int j = 0; j < newDataArray[i].length; j++) {
					if (null == newDataArray[i][j]) {
						newDataArray[i][j] = new ResultCell();
					}
				}
			}
			matrix.setData(newDataArray);
		}
		matrix.setRowCount(lastRowNumber);


		
		//PageCell pageCell = report.getPage(to);
		//RowCell fristRowCell = pageCell.getRow(0);
//		for(int k=0;k<(lastRowNumber-basicRowNumber);k++){
//			pageCell.getRows().add(fristRowCell);
//		}
//		
		Map<Integer, ResultCell> tempResultCellMap = new HashMap<Integer, ResultCell>();
		CellStyle tempStyle = null;
		for (int r = 0; r < lastRowNumber; r++) {
			Row sourceRow = sourceSheet.getRow(r);
			Row row = sheet.getRow(r);
			if (row == null) {
				row = sheet.createRow(r);
			}
			int colLastNum = sourceRow.getLastCellNum();
			for (int c = 0; c < colLastNum; c++) {

				ResultCell rc = null;
				if (r > (basicRowNumber - 1)) {
					rc = tempResultCellMap.get(c).create();
					rc.setRbegin(r);
					rc.setRend(r);
				} else {
					rc = report.getCell(to, r, c);
				}

				if (r == (basicRowNumber - 1)) {
					tempResultCellMap.put(c, rc);
				}
			//	System.out.println("r=============="+r);
			//	System.out.println("c=============="+c);
//				System.out.println("rc.getType()=============="+rc.getType());
//				System.out.println("rc.getCend()=============="+rc.getCend());
//				System.out.println("rc.getCbegin()=============="+rc.getCbegin());
//				System.out.println("rc.getCfrom()=============="+rc.getCfrom());
//				System.out.println("rc.getRfrom()=============="+rc.getRfrom());
//				System.out.println("rc.getRbegin()=============="+rc.getRbegin());
//				System.out.println("rc.getRend()=============="+rc.getRend());
//				System.out.println("-----------------------------------");
				matrix.getData()[r][c] = rc;
				boolean isCellNull = false;
				Cell cell = row.getCell(c);
				if (cell == null) {
					isCellNull = true;
					cell = row.createCell(c, CellType.BLANK);
				}else {
					if(null == tempStyle) {
						tempStyle = cell.getCellStyle();
					}
				}

				Cell sourceCell = sourceSheet.getRow(r).getCell(c);
				CellStyle style = sourceCell.getCellStyle();

				if (isCellNull && null != style) {
					if(isXssWorkbook) {
						XSSFCellStyle newstyle = (XSSFCellStyle) excel.createCellStyle();
						newstyle.cloneStyleFrom(style);
						cell.setCellStyle(newstyle);
					}else {
						XSSFCellStyle newstyle = (XSSFCellStyle) excel.createCellStyle();
						newstyle.cloneStyleFrom(tempStyle);
						cell.setCellStyle(newstyle);
					}
//					XSSFCellStyle newstyle = (XSSFCellStyle) excel.createCellStyle();
//					newstyle.cloneStyleFrom(style);
//					cell.setCellStyle(newstyle);
					// cell.getCellStyle().cloneStyleFrom(style);
				}
				setExcelCellResultValue(cell, sourceCell, evaluator);
			}
		}
		
		if (lastRowNumber < basicRowNumber) {
			for(int j= lastRowNumber;j<basicRowNumber;j++){
				SheetUtils.removeRow(sheet,j);
			}
			//sheet.shiftRows(lastRowNumber, (basicRowNumber-1), -1);
		}
		report.updateMiddle(excel);
		return report;
     /*	
		for (int r = 0; r < rowCount; r++) {
			for (int c = 0; c < colCount; c++) {
				//ResultCell rc = report.getCell(to, r, c);
				Cell cell = sheet.getRow(r).getCell(c);
				if(null!= cell && null!= sourceSheet.getRow(r) && null!=sourceSheet.getRow(r).getCell(c)){
					Cell sourceCell = sourceSheet.getRow(r).getCell(c);
					setExcelCellResultValue(cell, sourceCell, evaluator);
				}
//				try {
//					setExcelCellResultValue(cell, sourceSheet.getRow(r).getCell(c), evaluator);
//					// cell.setCellValue(getExcelCellRawValue(sourceSheet.getRow(r).getCell(c)));
//				} catch (Exception e) {
//					e.printStackTrace();
//					// TODO: handle exception
//				}
			}
		}*/
		// }

	}

	static void setExcelCellResultValue(Cell newCell, Cell cell, FormulaEvaluator evaluator) {

		if (cell == null) {
			return;
		}
		CellType type = cell.getCellTypeEnum();
		switch (type) {
			case BLANK:
				newCell.setCellValue("");
				break;
			case BOOLEAN:
				newCell.setCellValue(cell.getBooleanCellValue());
				break;
			case ERROR:
				if(cell instanceof XSSFCell && newCell instanceof XSSFCell){
					newCell.setCellValue(((XSSFCell)cell).getErrorCellString());
				}else if(cell instanceof HSSFCell && newCell instanceof HSSFCell){
					newCell.setCellValue(cell.getErrorCellValue()+"");
				}
				break;
			case FORMULA: {
				CellValue cv = evaluator.evaluate(cell);
				switch (cv.getCellTypeEnum()) {
					case BLANK:
						break;
					case BOOLEAN:
						newCell.setCellValue(cv.getBooleanValue());
						break;
					case ERROR:
						newCell.setCellValue(cv.formatAsString());
						break;
					case NUMERIC:
						newCell.setCellValue(cv.getNumberValue());
						break;
					case STRING:
						newCell.setCellValue(cv.getStringValue());
						break;
					default:
						newCell.setCellValue(cv.formatAsString());
						break;
				}
				break;
			}
			case NUMERIC:
				newCell.setCellValue(cell.getNumericCellValue());
				break;
			case STRING:
				newCell.setCellValue(cell.getStringCellValue());
				break;
			default:
				break;
		}
	}

	/**
	 * 导出列表数据
	 * @param out
	 * @param headers
	 * @param dataList
	 * @throws Exception
	 */
	public static void exportExcelByList(OutputStream out, String[] headers, List<List<Object>> dataList) throws Exception {

		// 声明一个工作薄
		XSSFWorkbook workBook = new XSSFWorkbook();
		// 生成一个表格
		XSSFSheet sheet = workBook.createSheet();
		workBook.setSheetName(0, "验证结果");
		// 创建表格标题行 第一行
		XSSFRow titleRow = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			titleRow.createCell(i).setCellValue(headers[i]);
		}
		int currentRowNum = 1;
		// 设置表格默认列宽度
		sheet.setDefaultColumnWidth(30);
		for (List<Object> dataRow : dataList) {
			Row row = sheet.createRow(currentRowNum);
			for (int j = 0; j < dataRow.size(); j++) {
				Cell contentCell = row.createCell(j);
				Object dataObject = dataRow.get(j);
				if (null != dataObject) {
					if (dataObject instanceof Integer) {
						contentCell.setCellValue(Integer.parseInt(dataObject.toString()));
					} else if (dataObject instanceof Double) {
						// contentCell.setCellStyle(contentDoubleStyle);
						contentCell.setCellValue(Double.parseDouble(dataObject.toString()));
					} else if (dataObject instanceof Date) {
						// contentCell.setCellStyle(contentStyle);
						contentCell.setCellValue(DateUtil.dateToString((Date) dataObject, DateUtil.FORMAT_ONE));
					} else {
						// contentCell.setCellStyle(contentStyle);
						contentCell.setCellValue(dataObject.toString());
					}
				} else {
					// contentCell.setCellStyle(contentStyle);
					contentCell.setCellValue("");
				}
			}
			currentRowNum = currentRowNum + 1;
		}
		workBook.write(out);
		out.flush();
		workBook.close();
	}
	
	
	/**
	 * 获取模板验证有效坐标
	 * @param filename
	 * @return
	 * @throws Exception
	 */
	public static int[] getValidateCoordinate(String filename) throws Exception{
		LOGGER.info("load template from file ["+filename+"]");
		XSSFWorkbook excel;
		try {
			excel=new XSSFWorkbook(new FileInputStream(filename));
		} catch (Exception e) {
			throw new Exception("文件["+filename+"]不是有效的(excel2007+)文件",e);
		}
		return Piston.getValidateCoordinate(excel);
	}
	
	/**
	 * 获取模板验证有效坐标
	 * @param templateBytes
	 * @return
	 * @throws Exception
	 */
	public static int[] getValidateCoordinateByBytes(byte[] templateBytes) throws Exception {
		ByteArrayInputStream in = new ByteArrayInputStream(templateBytes);

		XSSFWorkbook excel;
		try {
			excel = new XSSFWorkbook(in);
		} catch (Exception e) {
			LOGGER.error("构建模板失败", e);
			throw new Exception("构建模板失败", e);
		}
		return Piston.getValidateCoordinate(excel);
	}
}
